#!/usr/bin/env python
#coding=utf-8
#Iterator是迭代器的意思，它的作用是一次产生一个数据项，直到没有为止。这样在 for 循环中就可以对它进行循环处理了。那么它与一般的序列类型(list, tuple等)有什么区别呢？它一次只返回一个数据项，占用更少的内存。但它需要记住当前的状态，以便返回下一数据项。它是一个有着next()方法的对象。而序列类型则保存了所有的数据项，它们的访问是通过索引进行的。
#使用Iterator的好处除了节省内存外，还有一个好处就是可以把非线性化的处理转换成线性化的方式来进行处理。如对一棵树的访问，传统的方法可以使用递归函数来处理，下面是对树的一个中序遍历的示例：
from sqlalchemy import *

engine = create_engine('sqlite:///SimpleSite/development.db', echo=True)
#创建元数据
metadata = MetaData()
#绑定到engine
metadata.bind = engine
#执行SQL 语句的CREATE ，创建定义好的表
#metadata.create_all() 

# describe a table called 'users', query the database for its columns
users_table = Table('users', metadata, autoload=True)
# generate a SELECT statement and execute
result = users_table.select().execute()
#绑定engine是完全非强制性的，所有的操作也可以利用metadata的上下限来操作
metadata.create_all(engine)  # issue CREATE statements for all tables
# describe a table called 'users',  query the database for its columns
users_table = Table('users', metadata, autoload=True, autoload_with=engine)
# generate a SELECT statement and execute
result = engine.execute(users_table.select())


#A Table object can be created without specifying any of its contained attributes, using the argument autoload=True in conjunction with the table’s name and possibly its schema (if not the databases “default” schema). (You can also specify a list or set of column names to autoload as the kwarg include_columns, if you only want to load a subset of the columns in the actual database.) This will issue the appropriate queries to the database in order to locate all properties of the table required for SQLAlchemy to use it effectively, including its column names and datatypes, foreign and primary key constraints, and in some cases its default-value generating attributes. To use autoload=True, the table’s MetaData object need be bound to an Engine or Connection, or alternatively the autoload_with=<some connectable> argument can be passed. Below we illustrate autoloading a table and then iterating through the names of its columns:
#一个Table对象可以在没有指定任何其包含的属性的情况下用 autoload=True，获取其所有属性
messages = Table('users', metadata, autoload=True)
print [c.name for c in messages.columns]

#通过 reflect，MetaData object也能获取所有tables 列表，并反映全部设置
#Load all available table definitions from the database.
metadata.reflect(bind=engine)
users_table = metadata.tables['users']

#metadata.reflect()也常用来清空数据库中的所有表
#for table in reversed(meta.sorted_tables):
#    someengine.execute(table.delete())

#得到数据库中的所有表
for i in metadata.table_iterator(reverse=True):
    print i 
#得到一个表中所有的字段
for c in users_table.c:
    print c
# get the table's primary key columns or foreign_keys
for primary_key in users_table.primary_key:
    print primary_key

Session = sessionmaker(bind=engine)#如果之前已经定义了engine,可用此种方式
#之前没有定义
#Session = sessionmaker()
#Later, when you create your engine with create_engine(), connect it to the Session using configure():
#事后,当用create_engine() 创建了engine后,用configure()再连接它即可
#Session.configure(bind=engine)

session = Session()#实例化




for obj in session:
    print obj

